Related

Cli parsing in FlexyFramework, PEAR Console_GetArg

DataObjects links.ini and Archiving database data - an ideas of march post..

SQL change management done right.

PEAR state of play, why move to PEAR2

Dataobjects, Flexy Releases - and Ext-Roo builder for FlexyFramework.

crazy require_once optimization rears it head again.

Multilanguage setup for Flexy and FlexyFramework.

Flexy, the condom of template engines updated

PHP as a template engine, or recipe for disaster?

Generating excel, again.

SQL change management done right.

Published 2011-02-13 00:00:00

I'm slowly going through mtrack at present, breaking the HTML rendering into templates, and making nice clean classes for the fetching of underlying data. All this is due to implement quite a few ideas once it's in a state to be easily changed.

The more deeply I go through mtrack though, there are parts I find that you look at  and think, "That's a really smart way to handing that problem". Like the change control auditing, where each component has a ID for the last updated (and created) This maps to a event table reord containing who/when data. Much cleaner than the more common practice of using two datetime and user fields.

However, as always, there are parts of the code where you want to pull you hair out and say, No way should you solve a problem like this. Mtrack's SQL change management is one of those areas.

It's approach is not uncommon, and I've seen it before. Basically it uses an XML file to define the schema and then has a series of files schema1.xml,schema2.xml,schema3.xml... and so on. 

The installer works out what the current version is, then compares schema's between the current and selected version. and works out how to update the database. There is a driver for PostgreSQL and SQLite. 

My fundamental issue with all this is that while on the face of things it does not seems like a bad idea, however, it ignores the plain simple fact that there is already a language used for Database Schema definitions and modifications, and it's called SQL!

Anyone who uses SQL can quickly read a SQL file and understand what it's doing, even if it's in a different dialect (SQLite etc...), but putting that type of information in a XML file just adds so much noise. Worse of all, it involves learning and remembering a small subset of knowledge, that is only relevant to a tiny problem domain. Then the worst thing is it's just plain annoying to read.

For my projects I'm luckly only having to deal with a single database vendor (MySQL usually). To manage changes I keep SQL updated, this file contains all the table definitions along with the later changes. To update the database, I just pipe it through the mysql command line with the '-f' (force) switch. This is a trivial, simple and effective way to keep the database schema in sync wit the project code. There are even wrappers in the Component Framework code to do this simple task for you

The big problem however is that SQL, for all these great benefits has turned out to be horribly incompatible between database vendors. If I carry on with the above idea of keeping my database modifications in a SQL file,  then I would end up with one for each of the databases I want to support. Then I not only have to keep each of the schema files up-to-date, but have to remember the syntax for multiple database vendors, and apply each change to every file, not really a good long term plan.

So rather than keep multiple files up-to-date, I wondered, why not convert the SQL schema changes from one dialect to another, and just keep an SQL file as I currently do, and make it feaasible for anyone to install using the database of their choice.

This is one of those 'why has no-one done this before moments', but for the life of me, I could not find anything that came up to quickly on google. So I had a look at what was 'close enough' for this idea to work, and what a supprise, most of the code for this is already in PEAR.

The SQL Parser package, as very basically introduced here, http://www.sjhannah.com/blog/?p=16, provides pretty much all the backend code for a solution to this. However, there was not previously any code in either the parser, or writer/compiler to actually deal with DDL commands like alter table etc.

I've just commited the changes for this, so you can now very easily extend the current SQL_Parser_Compiler class to output your favourite dialect of SQL, based on reading an SQL file containing the base changes.

For a example of how to use it in real life, here's a nice simple example from the forked mtrack codebase.

And here's the commit that makes it possible..

And finally, a good example of a SQL file that can be run through the parser/generator.

Comments

A better way
I use an XML file with structure definition so that I can make it consistent and use more advanced features, like inheritance (i.e. I define a 'user_id' field and then use that definition in all places where I put user id's.)

I've got code that turns that XML file into SQL and code that scans the database structure and turns it into XML. Then I automatically compare the definition with the existing structure and generate appropriate SQL commands to sync them. This way I can even sync two databases running on different engines (MySQL and PostgreSQL for example).

It does make one learn a specific XML format, but it's rather straightforward -- it's based on <template name="foo"/> elements and extends="foo" attributes. Extending a field means copying all its attributes, and templates can extend other templates recursively.
#0 - Pies ( Link) on 2011-02-14 18:57:46 Delete Comment

Add Your Comment

    Follow us